import os
from decimal import Decimal
from urllib.parse import quote

import numpy as np
from django.core.exceptions import ObjectDoesNotExist, ValidationError
from django.core.files.storage import default_storage
from django.db.models import Q

import openpyxl
import xlrd
from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage
from django.db import transaction
from django.db.migrations import serializer
from django.db.models import Sum
from django.forms import model_to_dict
from django.http import JsonResponse
from django.http.response import HttpResponse
from openpyxl.reader.excel import load_workbook
from rest_framework import viewsets, status
from rest_framework.response import Response
from rest_framework.status import HTTP_400_BAD_REQUEST

from conf.common_conf import res200, res400, res201, res204
from conf.http_conf import REQUEST_GET_SUCCESS, REQUEST_PUT_SUCCESS, REQUEST_DELETE_SUCCESS, REQUEST_POST_SUCCESS, \
    REQUEST_PUT_FAILED, REQUEST_POST_FAILED, FILE_DOWNLOAD_SUCCESS, FILE_DOWNLOAD_FAILED, FILE_PERMISSION_DENIED, \
    REQUEST_GET_FAILED, REQUEST_PUT_FAILED, REQUEST_POST_FAILED, FILE_DOWNLOAD_SUCCESS, FILE_DOWNLOAD_FAILED, \
    FILE_PERMISSION_DENIED, REQUEST_GET_FAILED, DATA_VALIDATION_FAILED
from course.models import TrainCourse, TrainCourseExpense, TrainCourseTeacherExpense, TrainCourseTrainee
from course.serializers import TrainCourseSerializer, TrainCourseExpenseSerializer, TrainCourseTeacherExpenseSerializer, \
    TrainCourseTraineeSerializer
from middleware.authentication import MyAuthentication
from train import settings
from user.models import Trainee, Teacher, User
from user.serializers import TraineeSerializer
import pandas as pd
from datetime import datetime
from collections import Counter


class TrainCourseView(viewsets.ModelViewSet):

    serializer_class = TrainCourseSerializer
    authentication_classes = [MyAuthentication, ]
    def create(self, request, *args, **kwargs):
        try:
            with ((transaction.atomic())):
                train_course_serializer = TrainCourseSerializer(data=request.data)
                if (train_course_serializer.is_valid()):
                    new_instance = train_course_serializer.save()
                    train_course_id = new_instance.id

                    teacher_expense_total = 0
                    teacher_expense_datas = request.data.get('teacher_expenses', [])

                    for teacher_expense_data in teacher_expense_datas:
                        teacher_expense_data['train_course_id'] = train_course_id  # 添加新字段
                        teacher_expense_data['course_count'] = teacher_expense_data.get('course_count', '') or None
                        teacher_expense_data['teacher_unit_expense'] = teacher_expense_data.get(
                            'teacher_unit_expense', '') or None

                        teacher_expense_total = float(teacher_expense_total) + float(teacher_expense_data['teacher_expense'])

                        train_course_teacher_expense_serializer = TrainCourseTeacherExpenseSerializer(
                            data=teacher_expense_data)
                        if train_course_teacher_expense_serializer.is_valid():
                            train_course_teacher_expense_serializer.save()
                        else:
                            return JsonResponse(res400(REQUEST_POST_FAILED))

                    train_course_expense_data = request.data.copy()  # 如果 request.data 是字典，否则可能需要其他方法

                    try:
                        teacher_expense_total = float(teacher_expense_total)
                        course_site_rent = float(train_course_expense_data.get('course_site_rent', 0))
                        course_meal_fee = float(train_course_expense_data.get('course_meal_fee', 0))
                        management_cost = float(train_course_expense_data.get('management_cost', 0))
                        courseware_cost = float(train_course_expense_data.get('courseware_cost', 0))
                        printing_fee = float(train_course_expense_data.get('printing_fee', 0))
                        car_fare = float(train_course_expense_data.get('car_fare', 0))
                        quarterage = float(train_course_expense_data.get('quarterage', 0))
                        file_bag_fee = float(train_course_expense_data.get('file_bag_fee', 0))
                        tea_break_fee = float(train_course_expense_data.get('tea_break_fee', 0))
                        other_fee = float(train_course_expense_data.get('other_fee', 0))
                        tax = float(train_course_expense_data.get('tax', 0))
                        person_count = float(train_course_expense_data.get('person_count', 1))  # 防止除以0错误，默认至少1人

                    except ValueError as e:
                        print(f"Error converting string to float: {e}")
                        return JsonResponse(res400(DATA_VALIDATION_FAILED))

                    # 计算总成本
                    train_course_total_cost = teacher_expense_total + \
                                              course_site_rent + \
                                              course_meal_fee + \
                                              management_cost + \
                                              courseware_cost + \
                                              printing_fee + \
                                              car_fare + \
                                              quarterage + \
                                              file_bag_fee + \
                                              tea_break_fee + \
                                              tax + \
                                              other_fee

                    # 计算人均费用
                    personal_train_expense = train_course_total_cost / person_count
                    personal_train_expense = f"{personal_train_expense:.2f}"

                    train_course_expense_data['train_course_id'] = train_course_id  # 添加新字段
                    train_course_expense_data['train_course_total_cost'] =  f"{train_course_total_cost:.2f}"  # 添加新字段
                    train_course_expense_data['personal_train_expense'] = personal_train_expense  # 添加新字段

                    train_course_expense_serializer = TrainCourseExpenseSerializer(data=train_course_expense_data)
                    if train_course_expense_serializer.is_valid():
                        train_course_expense_serializer.save()
                    else:
                        return JsonResponse(res400(REQUEST_POST_FAILED))



                    file_url = request.data.get("file_url")

                    if not file_url:
                        train_course_trainee_datas = request.data.get('train_course_trainees', [])
                    else:
                        train_course_trainee_datas = parse_excel_to_trainees(file_url)

                    # 校验 train_course_trainees 列表的长度是否与 person_count 一致
                    person_count = request.data.get('person_count', 0)
                    if len(train_course_trainee_datas) != person_count:
                        error_message = "提供的学员数据数量与 person_count 不一致。"
                        return Response({"msg": error_message}, status=status.HTTP_400_BAD_REQUEST)
                    for train_course_trainee_data in train_course_trainee_datas:

                        credentials_num = generate_credentials_num(train_course_id)
                        train_course_trainee_data['credentials_num'] = credentials_num  # 添加新字段
                        train_course_trainee_data['train_course_id'] = train_course_id  # 添加新字段
                        train_course_trainee_data['personal_train_expense'] = personal_train_expense  # 添加新字段

                        if train_course_trainee_data.get('train_course_contents'):
                            train_course_ids = get_teacher_course_by_name(train_course_trainee_data.get('train_course_contents'), train_course_id)
                            train_course_trainee_data['course_ids'] = train_course_ids  # 添加新字段
                        train_course_trainee_serializer = TrainCourseTraineeSerializer(
                            data=train_course_trainee_data)
                        if train_course_trainee_serializer.is_valid():
                            new_train_course_trainee = train_course_trainee_serializer.save()
                            item = {'name': new_train_course_trainee.name, 'gender': new_train_course_trainee.gender,
                                    'unit_name': new_train_course_trainee.unit_name,'branch': new_train_course_trainee.branch,
                                    'source': new_train_course_trainee.source,
                                    'post': new_train_course_trainee.post,'phone_num': new_train_course_trainee.phone_num,
                                    'jcc_num': new_train_course_trainee.jcc_num, 'remark': new_train_course_trainee.remark}
                            trainee = Trainee.objects.filter(jcc_num=new_train_course_trainee.jcc_num)
                            if trainee.count() > 0:
                                # 存在学员更新
                                trainee.update(**item)
                            else:
                                # 不存在学员创建
                                Trainee.objects.create(**item)
                        else:
                            return JsonResponse(res400(REQUEST_POST_FAILED))
                    train_course_trainee_datas = TrainCourseTrainee.objects.filter(train_course_id=train_course_id)
                    for train_course_trainee_data in train_course_trainee_datas:
                        train_course_ids = train_course_trainee_data.course_ids
                        if train_course_ids:
                            train_course_ids_list = [int(id) for id in train_course_ids.split(",")]
                            averages = get_trainee_count_for_course_id(train_course_id)

                            course_train_expense = sum(
                                float(averages[id]) for id in train_course_ids_list if id in averages)
                            personal_train_expense = round((float(train_course_total_cost) - float(teacher_expense_total))/person_count + float(course_train_expense), 2)

                            train_course_trainee_data.personal_train_expense = personal_train_expense
                            train_course_trainee_data.save()
                        else:
                            train_course_trainee_data.personal_train_expense = personal_train_expense
                            train_course_trainee_data.save()
                    return JsonResponse(res201(REQUEST_POST_SUCCESS))

                else:
                    return JsonResponse(res400(REQUEST_POST_FAILED))


        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))

    def list(self, request, *args, **kwargs):
        """
            :param: pk or nothing.pk is user's id.
            :return:query success or query failed.
            :annotation:``Querying User Information`` interface.
            Query user information using the "user id" or all user information.
        """
        try:
            id, page, size,status, = kwargs.get('pk', ""), request.query_params.get('page', ""), request.query_params.get(
                'size', ""),request.query_params.get('status', "")
            if id != "":
                train_course_obj = TrainCourse.objects.filter(id=id).order_by("-id")
                if train_course_obj.count() > 0:
                    return JsonResponse(res200('成功', model_to_dict(train_course_obj.first())))
                else:
                    return JsonResponse(res400('暂无数据'))
            if status != "":
                results = list(TrainCourse.objects.filter(course_status=status).values().order_by('-id'))
                for item in results:
                    teacher_count = TrainCourseTeacherExpense.objects.filter(train_course_id=item.get("id")).count()
                    item['teacher_count'] = teacher_count
                return JsonResponse(res200(REQUEST_GET_SUCCESS, list(results)))

            res = TrainCourse.objects.all().order_by("-id")
            paginator = Paginator(res, int(size))
            try:
                tar_page = paginator.page(page)
            except PageNotAnInteger:
                tar_page = paginator.page(1)
            except EmptyPage:
                tar_page = paginator.page(paginator.num_pages)
            final_data = TrainCourseSerializer(tar_page, many=True)
            return JsonResponse(res200(REQUEST_GET_SUCCESS, final_data.data, res.count()))

        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))

    def get_course_details(self, request, *args, **kwargs):
        try:
            data = {}
            # 获取培训班基本信息
            train_course_id = request.query_params.get('train_course_id', '')

            # 获取费用信息，一个培训班只有一个费用记录
            train_course = TrainCourse.objects.get(id=train_course_id)
            train_course_data = model_to_dict(train_course)

            # 获取费用信息
            train_course_expense = TrainCourseExpense.objects.get(train_course_id=train_course_id)
            train_course_expense_data = model_to_dict(train_course_expense)

            # 获取讲师费用列表
            teacher_expenses = TrainCourseTeacherExpense.objects.filter(train_course_id=train_course_id)
            teacher_expenses_data = list(teacher_expenses.values())

            # 获取学员列表
            trainees = TrainCourseTrainee.objects.filter(train_course_id=train_course_id)
            trainees_data = list(trainees.values())

            # 额外的逻辑处理，比如为学员添加课程内容
            for trainee in trainees_data:
                course_ids = trainee.get('course_ids')
                if course_ids:
                    # 假设 get_teacher_course_by_id 是一个已经定义好的函数
                    train_course_contents = get_teacher_course_by_id(course_ids)
                    trainee["train_course_contents"] = train_course_contents
                else:
                    trainee["train_course_contents"] = []

            # 组装数据
            data['train_course'] = train_course_data
            data['train_course_expense'] = train_course_expense_data
            data['teacher_expenses'] = teacher_expenses_data
            data['train_course_trainees'] = trainees_data

            return Response(res200(REQUEST_GET_SUCCESS, data))

        except (TrainCourse.DoesNotExist, TrainCourseExpense.DoesNotExist, TrainCourseTeacherExpense.DoesNotExist):
            return JsonResponse(res400(msg="指定的培训班或相关记录不存在"))
        except Exception as e:
            # 记录日志或其他错误处理
            return JsonResponse(res400(msg="获取培训班详情时发生错误"))

    def search_train_courses(self,request,*args, **kwargs):
        search_term = self.request.query_params.get('search_term','')
        start_time = self.request.query_params.get('start_time')
        end_time = self.request.query_params.get('end_time')
        page_number = self.request.query_params.get('page','1')
        page_size = self.request.query_params.get('size','10')

        if search_term != "":

            try:
                page_size = int(page_size)  # 确保size是一个整数
            except ValueError:
                page_size = 10  # 如果size不是有效的数字，则使用默认值

            query = (
                    Q(course_name__icontains=search_term) |
                    Q(course_type__icontains=search_term) |
                    Q(sponsor__icontains=search_term) |
                    Q(organizer__icontains=search_term) |
                    Q(course_site__icontains=search_term)
            )
            # 执行查询
            results = TrainCourse.objects.filter(query)
        else:
            results = TrainCourse.objects.filter(course_start_time=start_time, course_end_time=end_time)
        # 初始化分页器
        paginator = Paginator(results, page_size)
        try:
            # 获取指定页码的结果
            page_results = paginator.page(page_number)
        except PageNotAnInteger:
            # 如果页码不是一个整数，则返回第一页
            page_results = paginator.page(1)
        except EmptyPage:
            # 如果页码超出范围，则返回最后一页
            page_results = paginator.page(paginator.num_pages)

        # 将查询结果转换为字典列表
        courses_data = [{"id": course.id, "course_name": course.course_name, "course_modality": course.course_modality, "course_type": course.course_type,
                         "course_start_time": course.course_start_time, "course_end_time": course.course_end_time,
                         "sponsor_type": course.sponsor_type, "sponsor": course.sponsor,
                         "organizer": course.organizer, "total_day": course.total_day,
                         "person_count": course.person_count, "course_site": course.course_site,
                         } for course in page_results]

        return JsonResponse(res200(REQUEST_GET_SUCCESS, courses_data, paginator.count))

    def update(self, request, *args, **kwargs):
        try:
            with transaction.atomic():
                id = request.data.get("id")
                instance = TrainCourse.objects.get(id=id)
                errors = []
                train_course_serializer = TrainCourseSerializer(instance, data=request.data, partial=True)
                if train_course_serializer.is_valid():
                    updated_instance = train_course_serializer.save()
                    train_course_id = updated_instance.id
                    train_course_expense_data = request.data.copy()
                    try:
                        train_course_teacher_expense_datas = TrainCourseTeacherExpense.objects.filter(
                            train_course_id=train_course_id)
                        teacher_expense_total = train_course_teacher_expense_datas.aggregate(Sum('teacher_expense'))[
                                                    'teacher_expense__sum'] or 0
                        teacher_expense_total = float(teacher_expense_total)
                        course_site_rent = float(train_course_expense_data.get('course_site_rent', 0))
                        course_meal_fee = float(train_course_expense_data.get('course_meal_fee', 0))
                        management_cost = float(train_course_expense_data.get('management_cost', 0))
                        courseware_cost = float(train_course_expense_data.get('courseware_cost', 0))
                        printing_fee = float(train_course_expense_data.get('printing_fee', 0))
                        car_fare = float(train_course_expense_data.get('car_fare', 0))
                        quarterage = float(train_course_expense_data.get('quarterage', 0))
                        file_bag_fee = float(train_course_expense_data.get('file_bag_fee', 0))
                        tea_break_fee = float(train_course_expense_data.get('tea_break_fee', 0))
                        other_fee = float(train_course_expense_data.get('other_fee', 0))
                        tax = float(train_course_expense_data.get('tax', 0))
                        person_count = float(train_course_expense_data.get('person_count', 1))  # 防止除以0错误，默认至少1人

                    except ValueError as e:
                        print(f"Error converting string to float: {e}")
                        return JsonResponse(res400(DATA_VALIDATION_FAILED))

                    # 计算总成本
                    train_course_total_cost = teacher_expense_total + \
                                              course_site_rent + \
                                              course_meal_fee + \
                                              management_cost + \
                                              courseware_cost + \
                                              printing_fee + \
                                              car_fare + \
                                              quarterage + \
                                              file_bag_fee + \
                                              tea_break_fee + \
                                              tax + \
                                              other_fee

                    # 计算人均费用
                    personal_train_expense = train_course_total_cost / person_count
                    personal_train_expense = f"{personal_train_expense:.2f}"

                    train_course_expense_data['train_course_id'] = train_course_id  # 添加新字段
                    train_course_expense_data['train_course_total_cost'] = f"{train_course_total_cost:.2f}"  # 添加新字段
                    train_course_expense_data['personal_train_expense'] = personal_train_expense  # 添加新字段

                    train_course_expense, _ = TrainCourseExpense.objects.get_or_create(train_course_id=train_course_id,
                                                                                       defaults=train_course_expense_data)
                    train_course_expense_serializer = TrainCourseExpenseSerializer(train_course_expense,
                                                                                   data=train_course_expense_data,
                                                                                   partial=True)
                    if train_course_expense_serializer.is_valid():
                        train_course_expense_serializer.save()
                    else:
                        errors.append("更新TrainCourseExpense失败: " + str(train_course_expense_serializer.errors))

                    teacher_expense_datas = request.data.get('teacher_expenses', [])

                    TrainCourseTeacherExpense.objects.filter(train_course_id=train_course_id).delete()
                    for teacher_expense_data in teacher_expense_datas:

                        teacher_expense_data['train_course_id'] = train_course_id  # 添加新字段
                        teacher_expense_data['course_count'] = teacher_expense_data.get('course_count', '') or None
                        teacher_expense_data['teacher_unit_expense'] = teacher_expense_data.get(
                            'teacher_unit_expense', '') or None

                        train_course_teacher_expense_serializer = TrainCourseTeacherExpenseSerializer(
                            data=teacher_expense_data)
                        if train_course_teacher_expense_serializer.is_valid():
                            train_course_teacher_expense_serializer.save()
                        else:
                            errors.append("更新TrainCourseTeacherExpense失败: " + str(
                                train_course_teacher_expense_serializer.errors))
                    if errors:
                        raise ValidationError(errors)
                    TrainCourseTrainee.objects.filter(train_course_id=train_course_id).delete()
                    file_url = request.data.get("file_url")
                    if not file_url:
                        train_course_trainee_datas = request.data.get('train_course_trainees', [])
                    else:
                        train_course_trainee_datas = parse_excel_to_trainees(file_url)
                    # 校验 train_course_trainees 列表的长度是否与 person_count 一致
                    person_count = request.data.get('person_count', 0)
                    if len(train_course_trainee_datas) != person_count:
                        error_message = "提供的学员数据数量与 person_count 不一致。"
                        return Response({"msg": error_message}, status=status.HTTP_400_BAD_REQUEST)

                    for train_course_trainee_data in train_course_trainee_datas:
                        credentials_num = generate_credentials_num(train_course_id)
                        train_course_trainee_data['credentials_num'] = credentials_num  # 添加新字段
                        train_course_trainee_data['train_course_id'] = train_course_id  # 添加新字段
                        if train_course_trainee_data.get('train_course_contents', []):
                            train_course_ids = get_teacher_course_by_name(train_course_trainee_data.get('train_course_contents', []), train_course_id)
                            train_course_trainee_data['course_ids'] = train_course_ids  # 添加新字段

                        train_course_trainee_serializer = TrainCourseTraineeSerializer(
                            data=train_course_trainee_data)

                        if train_course_trainee_serializer.is_valid():
                            new_train_course_trainee = train_course_trainee_serializer.save()
                            item = {'name': new_train_course_trainee.name, 'gender': new_train_course_trainee.gender,
                                    'unit_name': new_train_course_trainee.unit_name,
                                    'branch': new_train_course_trainee.branch,
                                    'source': new_train_course_trainee.source, 'post': new_train_course_trainee.post,
                                    'phone_num': new_train_course_trainee.phone_num,
                                    'jcc_num': new_train_course_trainee.jcc_num,
                                    'remark': new_train_course_trainee.remark}
                            trainee = Trainee.objects.filter(jcc_num=new_train_course_trainee.jcc_num)
                            if trainee.count() > 0:
                                trainee.update(**item)
                            else:
                                Trainee.objects.create(**item)
                        else:
                            print(train_course_trainee_serializer.errors)
                    train_course_trainee_datas = TrainCourseTrainee.objects.filter(train_course_id=train_course_id)
                    for train_course_trainee_data in train_course_trainee_datas:
                        train_course_ids = train_course_trainee_data.course_ids
                        if train_course_ids:
                            train_course_ids_list = [int(id) for id in train_course_ids.split(",")]
                            averages = get_trainee_count_for_course_id(train_course_id)

                            course_train_expense = sum(
                                float(averages[id]) for id in train_course_ids_list if id in averages)
                            personal_train_expense = round((float(train_course_total_cost) - float(teacher_expense_total))/person_count + float(course_train_expense), 2)

                            train_course_trainee_data.personal_train_expense = personal_train_expense
                            train_course_trainee_data.save()
                        else:
                            train_course_trainee_data.personal_train_expense = personal_train_expense
                            train_course_trainee_data.save()
                    return Response(res201(REQUEST_PUT_SUCCESS), status=status.HTTP_200_OK)
                else:
                    return Response(res400(train_course_serializer.errors), status=status.HTTP_400_BAD_REQUEST)
        except TrainCourse.DoesNotExist:
            return Response(res400("课程未找到"), status=status.HTTP_404_NOT_FOUND)
        except Exception as e:
            return Response(res400(str(e)), status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    def destroy(self, request, *args, **kwargs):
        """
        :param: pk.pk is user's id.
        :return:delete success or delete failed.
        :annotation:``Delete User Information`` interface.
        Delete user using the "user id".
        """
        # 尝试获取要删除的TrainCourse实例
        train_course_ids = request.data.get("ids", [])

        # 删除与之关联的TrainCourseExpense记录
        TrainCourseExpense.objects.filter(train_course_id__in=train_course_ids).delete()

        # 删除与之关联的TrainCourseTeacherExpense记录
        TrainCourseTeacherExpense.objects.filter(train_course_id__in=train_course_ids).delete()

        # 删除与之关联的TrainCourseTrainee记录
        TrainCourseTrainee.objects.filter(train_course_id__in=train_course_ids).delete()

        # 最后删除TrainCourse记录
        TrainCourse.objects.filter(id__in=train_course_ids).delete()

        return JsonResponse(res204(REQUEST_DELETE_SUCCESS))

class TrainCourseImportView(viewsets.ModelViewSet):
    @transaction.atomic  # 添加事务装饰器
    def import_from_excel(self, request, *args, **kwargs):
        excel_file = request.FILES.get('file')
        if not excel_file:
            return Response({"code":400,"msg": "No file uploaded"}, status=HTTP_400_BAD_REQUEST)

        try:
            xls = pd.ExcelFile(excel_file)
        except Exception as e:
            return Response({"code":400,"msg": f"Error reading Excel file: {str(e)}"}, status=HTTP_400_BAD_REQUEST)

        results = {}
        train_course_id = None

        for sheet_name in xls.sheet_names:
            df = xls.parse(sheet_name)
            df = df.applymap(lambda x: None if pd.isna(x) else x)

            if sheet_name == '培训班基础信息':
                try:
                    results['train_course'] = import_train_courses(df)
                    train_course_id = results['train_course'].get('train_course_id')
                except ValueError as e:
                    transaction.set_rollback(True)
                    return Response({"msg": str(e)}, status=status.HTTP_400_BAD_REQUEST)
            elif sheet_name == '培训班基础费用':
                try:
                    results['train_course_expense'] = import_train_course_expenses(train_course_id, df)
                except ValueError as e:
                    transaction.set_rollback(True)
                    return Response({"code":400,"msg": str(e)}, status=status.HTTP_400_BAD_REQUEST)
            elif sheet_name == '培训班教师信息':
                try:
                    results['train_course_teacher_expense'] = import_train_course_teacher_expenses(train_course_id, df)
                except ValueError as e:
                    transaction.set_rollback(True)  # 因教师不存在，标记事务回滚
                    return Response({"msg": str(e)}, status=status.HTTP_400_BAD_REQUEST)
            elif sheet_name == '培训班学员信息':
                try:
                    results['train_course_trainee'] = import_train_course_trainees(train_course_id, df)
                except ValueError as e:
                    transaction.set_rollback(True)
                    return Response({"msg": str(e)}, status=status.HTTP_400_BAD_REQUEST)
            else:
                results[sheet_name] = {"status": "Skipped", "message": "Unsupported sheet"}

        train_course_teacher_expense_datas = TrainCourseTeacherExpense.objects.filter(train_course_id=train_course_id)
        person_count = TrainCourseTrainee.objects.filter(train_course_id=train_course_id).count()

        teacher_expense_total = train_course_teacher_expense_datas.aggregate(Sum('teacher_expense'))[
                                    'teacher_expense__sum'] or 0
        train_course_expense = TrainCourseExpense.objects.filter(train_course_id=train_course_id).aggregate(
            total=Sum('course_site_rent') + Sum('course_meal_fee') +
                  Sum('management_cost') + Sum('courseware_cost') +
                  Sum('printing_fee') + Sum('car_fare') +
                  Sum('quarterage') + Sum('file_bag_fee') +
                  Sum('tea_break_fee') + Sum('other_fee') +
                  Sum('tax')
        )['total'] or Decimal('0.00')
        train_course_total_cost = teacher_expense_total + train_course_expense
        if person_count > 0:
            personal_train_expense = train_course_total_cost/person_count
            personal_train_expense = f"{personal_train_expense:.2f}"
        else:
            personal_train_expense = 0
        train_course_expense_data = TrainCourseExpense.objects.get(train_course_id=train_course_id)
        train_course_expense_data.train_course_total_cost = f"{train_course_total_cost:.2f}"
        train_course_expense_data.personal_train_expense = personal_train_expense
        train_course_expense_data.save()
        train_course_trainee_datas = TrainCourseTrainee.objects.filter(train_course_id=train_course_id)
        for train_course_trainee_data in train_course_trainee_datas:
            train_course_ids = train_course_trainee_data.course_ids
            if train_course_ids:
                train_course_ids_list = [int(id) for id in train_course_ids.split(",")]
                averages = get_trainee_count_for_course_id(train_course_id)

                course_train_expense = sum(
                    float(averages[id]) for id in train_course_ids_list if id in averages)
                personal_train_expense = round(
                    (float(train_course_total_cost) - float(teacher_expense_total)) / person_count + float(
                        course_train_expense), 2)

                train_course_trainee_data.personal_train_expense = personal_train_expense
                train_course_trainee_data.save()
            else:
                train_course_trainee_data.personal_train_expense = personal_train_expense
                train_course_trainee_data.save()

        return JsonResponse(res200(REQUEST_POST_SUCCESS, results))


def import_train_courses(df):
    success_count = 0
    fail_count = 0
    created_ids = []
    # 遍历DataFrame的每一行
    for index, row in df.iterrows():

        train_course_data = {
            'course_name': row['培训班名称'],
            'course_modality': row['培训班形式'],
            'course_type': row['培训班类型'],
            'course_start_time': row['培训开始时间'],
            'course_end_time': row['培训结束时间'],
            'sponsor_type': row['主办单位类型'],
            'sponsor': row['主办单位'],
            'organizer': row['承办单位'],
            'total_day': row['合计天数'],
            'person_count': row['人数'],
            'course_site': row['培训地点'],
            'credentials': string_to_boolean(row['是否有证书']),
            'user1_name': row['班主任1'],
            'user2_name': row['班主任2']

        }
        try:

            user1 = User.objects.filter(name=row['班主任1']).first()
            user2 = User.objects.filter(name=row['班主任2']).first()

            train_course_data['user1_id'] = user1.id
            train_course_data['user2_id'] = user2.id
        except Exception:
            raise ValueError("班主任不存在")

        new_instance = TrainCourse.objects.create(**train_course_data)
        train_course_id = new_instance.id
        print(11111111111111111111, train_course_id)
        if not train_course_id:
            fail_count += 1
        else:
            success_count += 1
        # train_course_serializer = TrainCourseSerializer(data=train_course_data)
        # if train_course_serializer.is_valid():
        #     new_instance = train_course_serializer.save()
        #     created_ids.append(new_instance.id)  # 收集新增记录的ID
        #     success_count += 1
        # else:
        #     fail_count += 1
        #     # 可能想要记录具体的错误信息，这里仅增加计数

    # 返回导入结果统计
    error_message = "" if fail_count == 0 else f"在导入培训班费用过程中遇到问题，未能成功创建。"
    if error_message != "":
        raise ValueError(error_message)
    return {"success_count": success_count, 'train_course_id':train_course_id, "fail_count": fail_count}
def import_train_course_expenses(train_course_id, df):
    success_count = 0
    fail_count = 0

    # 遍历DataFrame的每一行
    for index, row in df.iterrows():
        # 准备数据，注意转换费用字段为Decimal
        train_course_expense_data = {
            'train_course_id': train_course_id,
            'course_site_rent': row['培训会场租金'],
            'course_meal_fee': row['培训餐费'],
            'management_cost': row['培训管理费'],
            'courseware_cost': row['视频课件制作费'],
            'printing_fee': row['资料打印费'],
            'car_fare': row['老师及工作人员交通费'],
            'quarterage': row['老师及工作人员住宿费'],
            'file_bag_fee': row['资料袋套装'],
            'tea_break_fee': row['茶歇费'],  # 注意修正了字段名，原问题中两次使用了'资料袋套装'
            'other_fee': row['其他费用'],
            'other_fee_remark': row['其他费用备注'],
            'tax': row['开票税额']
        }

        # 将费用字段转换为Decimal类型，以保持精度
        for key in train_course_expense_data.keys():
            if isinstance(train_course_expense_data[key], (int, float)):
                train_course_expense_data[key] = Decimal(str(train_course_expense_data[key]))

        new_instance = TrainCourseExpense.objects.create(**train_course_expense_data)
        id = new_instance.id
        if not id:
            fail_count += 1
        else:
            success_count += 1
            # 可以记录具体的错误信息，这里仅增加计数
    error_message = "" if fail_count == 0 else f"在导入培训班费用过程中遇到问题，未能成功创建。"
    if error_message != "":
        raise ValueError(error_message)

    # 返回导入结果统计
    # 返回导入结果统计及概要错误信息
    return {
        "success_count": success_count,
        "fail_count": fail_count,
        "error_summary": error_message,
    }
def import_train_course_teacher_expenses(train_course_id, df):
    success_count = 0
    fail_count = 0
    for index, row in df.iterrows():
        # 准备数据

        train_course_new_teacher_expense_data = {
            'name': row['讲师名称'],
            'gender': row['性别'],
            'internal_trainer': string_to_boolean(row['是否内训师']),
            'source': row['来源'],
            'unit_name': row['单位名称'],
            'branch': row['部门'],
            'post': row['职务'],
            'phone_num': row['手机号'],
            'cost': int(row['课费(元)']) if pd.notnull(row['课费(元)']) else 0,
            'id_num': row['身份证号'],
            'bank_info': row['开户行信息'],
            'self_intro': row['个人介绍'],
            'remark': row['备注']
        }
        teacher = Teacher.objects.filter(phone_num=row['手机号'])
        if teacher.count() > 0:

            teacher.update(**train_course_new_teacher_expense_data)

        else:
            # 不存在学员创建
            Teacher.objects.create(**train_course_new_teacher_expense_data)

        try:
            teacher = Teacher.objects.get(phone_num=row['手机号'])
            teacher_id = teacher.id
        except Teacher.DoesNotExist:
            return Response(res400("教师不存在未找到"), status=status.HTTP_404_NOT_FOUND)

        train_course_teacher_expense_data = {

            'train_course_id': train_course_id,
            'teacher_id': teacher_id,
            'train_course_content': row['培训课程内容'],
            'teacher_name': row['讲师名称'],
            'course_count': row['课时'],
            'teacher_unit_expense': row['课酬单价'],
            'is_total': string_to_boolean(row['是/否一口价']),
            'teacher_expense': Decimal(int(row['课酬总价'])) if pd.notnull(row['课酬总价']) else Decimal('0.00'),
        }
        new_instance = TrainCourseTeacherExpense.objects.create(**train_course_teacher_expense_data)
        if new_instance.pk:  # 使用pk属性检查实例是否成功创建
            success_count += 1
        else:
            fail_count += 1
            # 记录日志或处理其他可能的错误
        error_message = "" if fail_count == 0 else f"在导入教师课酬表过程中遇到问题，未能成功创建。"
        if error_message != "":
            raise ValueError(error_message)

    return {'success_count': success_count, 'fail_count': fail_count}


def import_train_course_trainees(train_course_id, df):
    success_count = 0
    fail_count = 0
    expected_count = len(df)
    # 获取TrainCourse表中对应train_course_id的person_count值
    train_course = TrainCourse.objects.get(id=train_course_id)
    person_count = train_course.person_count
    error_message = "" if expected_count == person_count else f"培训班人数和学员表的学员数量不一致，请核实表格信息。"
    if error_message != "":
        raise ValueError(error_message)
    # 遍历DataFrame的每一行
    for index, row in df.iterrows():
        # 准备数据
        credentials_num = generate_credentials_num(train_course_id)
        train_course_trainee_data = {
            'train_course_id': train_course_id,
            'name': row['学员姓名'],
            'gender': row['性别'],
            'source': row['来源'],
            'unit_name': row['单位名称'],
            'branch': row['所属部门'],
            'post': row['职务'],
            'phone_num': row['手机号'],
            'jcc_num': row['交建通账号'],
            'remark': row['备注'],
            'credentials_num': credentials_num
        }
        train_course_new_trainee_data = {
            'name': row['学员姓名'],
            'gender': row['性别'],
            'source': row['来源'],
            'unit_name': row['单位名称'],
            'branch': row['所属部门'],
            'post': row['职务'],
            'phone_num': row['手机号'],
            'jcc_num': row['交建通账号'],
            'remark': row['备注']
        }
        new_instance = TrainCourseTrainee.objects.create(**train_course_trainee_data)
        id = new_instance.id
        if not id:
            fail_count += 1
        else:
            success_count += 1
        error_message = "" if fail_count == 0 else f"在导入培训班学员表过程中遇到问题，未能成功创建。"
        if error_message != "":
            raise ValueError(error_message)
            # 可以记录具体的错误信息，这里仅增加计数
        trainee = Trainee.objects.filter(jcc_num=row['交建通账号'])
        if trainee.count() > 0:
            # 存在学员更新
            trainee.update(**train_course_new_trainee_data)
        else:
            # 不存在学员创建
            Trainee.objects.create(**train_course_new_trainee_data)


    # 返回导入结果统计
    return {"success_count": success_count, "fail_count": fail_count}

class TrainCourseDownLoadView(viewsets.ModelViewSet):
        def generate_template(self, request, *args, **kwargs):
            try:
                # # 定义每个sheet的数据结构
                # train_course_header = ['培训班名称', '培训班形式', '培训班类型', '培训开始时间', '培训结束时间', '主办单位类型',
                #                        '主办单位', '承办单位', '合计天数', '人数', '培训地点', '班主任1', '班主任2', '是否有证书']
                # train_course_expense_header = ['培训会场租金', '培训餐费', '培训管理费', '视频课件制作费', '资料打印费',
                #                                '老师及工作人员交通费', '老师及工作人员住宿费', '资料袋套装', '茶歇费',
                #                                '其他费用', '其他费用备注', '开票税额']
                # train_course_teacher_expense_header = ['培训课程内容', '讲师名称', '课时', '课酬单价', '是/否一口价', '课酬总价',
                #                                        '性别', '是否内训师', '来源', '单位名称', '部门','职务', '手机号',
                #                                        '课费(元)', '身份证号', '开户行信息', '个人介绍', '备注']
                # train_course_trainee_header = ['学员姓名', '性别', '来源', '单位名称', '所属部门', '职务',
                #                                '手机号', '交建通账号', '备注']
                #
                # # 创建DataFrame作为模板数据，这里使用空数据
                # df_train_course = pd.DataFrame(columns=train_course_header)
                # df_train_course_expense = pd.DataFrame(columns=train_course_expense_header)
                # df_train_course_teacher_expense = pd.DataFrame(columns=train_course_teacher_expense_header)
                # df_train_course_trainee = pd.DataFrame(columns=train_course_trainee_header)
                #
                # # 创建ExcelWriter对象
                # file_path = os.path.join(settings.MEDIA_ROOT, 'model\\train_course_template.xlsx')
                #
                # writer = pd.ExcelWriter(file_path, engine='openpyxl')
                #
                # # 将DataFrame写入不同的sheet
                # df_train_course.to_excel(writer, sheet_name='培训班基础信息', index=False)
                # df_train_course_expense.to_excel(writer, sheet_name='培训班基础费用', index=False)
                # df_train_course_teacher_expense.to_excel(writer, sheet_name='培训班教师信息', index=False)
                # df_train_course_trainee.to_excel(writer, sheet_name='培训班学员信息', index=False)
                #
                # # 保存Excel文件到MEDIA_ROOT目录下的特定位置
                # writer.save()
                # # 重新加载工作簿以设置列宽
                # wb = load_workbook(file_path)
                #
                # # 设置每个sheet的单元格宽度的函数
                # for sheet_name in wb.sheetnames:
                #     ws = wb[sheet_name]
                #     set_column_widths(ws, default_width=25)  # 你可以根据需要调整default_width的值
                #
                # # 保存更改后的工作簿
                # wb.save(file_path)
                # 获取文件的URL
                # file_url = default_storage.url('train_course_template.xlsx')
                file_url = 'train_course_template.xlsx'

                return JsonResponse(res201(FILE_DOWNLOAD_SUCCESS, {'file_url': 'model/'+file_url}))

            except PermissionError:
                print("没有权限写入文件，请检查文件权限设置。")
                return JsonResponse(res400(FILE_PERMISSION_DENIED))

            except Exception as e:
                print(f"发生未知错误：{str(e)}")
                return JsonResponse(res400(FILE_DOWNLOAD_FAILED))

        def generate_train_courses(self, request, *args, **kwargs):
            train_course_ids = request.data.get("ids", [])
            # 定义数据库字段到中文表头的映射
            train_course_column_mapping = {
                'course_name': '培训班名称',
                'course_type': '培训班形式',
                'course_time': '培训时间',

                'course_start_time': '培训开始时间',
                'course_end_time': '培训结束时间',
                'sponsor_type': '主办单位类型',
                'sponsor': '主办单位',
                'organizer': '承办单位',
                'total_day': '合计天数',
                'person_count': '人数',
                'course_site': '培训地点',
                'user_name': '班主任',

            }
            train_course_expense_column_mapping = {
                'course_site_rent': '培训会场租金',
                'course_meal_fee': '培训餐费',
                'management_cost': '培训管理费',
                'courseware_cost': '视频课件制作费',
                'printing_fee': '资料打印费',
                'car_fare': '老师及工作人员交通费',
                'quarterage': '老师及工作人员住宿费',
                'file_bag_fee': '资料袋套装',
                'tea_break_fee': '茶歇费',
                'other_fee': '其他费用',
                'tax': '开票税额',
            }
            train_course_teacher_expense_column_mapping = {
                'train_course_content': '培训课程内容',
                'teacher_id': '讲师ID',
                'teacher_name': '讲师名称',
                'teacher_expense': '课酬',
            }
            train_course_trainee_column_mapping = {
                'train_course_id': '培训班ID',
                'name': '学员姓名',
                'gender': '性别',
                'source': '来源',
                'unit_name': '单位名称',  # 注意：原字段注释为'所在单位'，这里保持与模型定义一致使用'单位名称'
                'branch': '所属部门',
                'post': '职务',
                'phone_num': '手机号',
                'jcc_num': '交建通账号',
                'remark': '备注',
            }

            # 遍历每个ID执行查询和数据填充
            all_train_course_data = []
            all_expense_data = []
            all_teacher_expense_data = []
            all_trainee_data = []

            for train_course_id in train_course_ids:
                # 查询数据库
                train_course = TrainCourse.objects.filter(id=train_course_id).values()
                train_course_expense = TrainCourseExpense.objects.filter(train_course_id=train_course_id).values()
                train_course_teacher_expenses = TrainCourseTeacherExpense.objects.filter(train_course_id=train_course_id).values()
                train_course_trainees = TrainCourseTrainee.objects.filter(train_course_id=train_course_id).values()

                # 收集数据到列表
                all_train_course_data.extend(list(train_course))
                all_expense_data.extend(list(train_course_expense))
                all_teacher_expense_data.extend(list(train_course_teacher_expenses))
                all_trainee_data.extend(list(train_course_trainees))

            # 如果有数据，创建DataFrame并填充；否则提示无数据
            if all_train_course_data:
                columns_to_drop_train_course = ['id', 'user_id', 'course_modality', 'credentials']

                df_train_course = pd.DataFrame(all_train_course_data)
                df_train_course.drop(columns=columns_to_drop_train_course, inplace=True, errors='ignore')
                df_train_course.rename(columns=train_course_column_mapping, inplace=True)

                columns_to_drop_train_course_expense = ['train_course_total_cost', 'personal_train_expense', 'id', 'train_course_id']
                df_train_course_expense = pd.DataFrame(all_expense_data)
                df_train_course_expense.drop(columns=columns_to_drop_train_course_expense, inplace=True, errors='ignore')
                df_train_course_expense.rename(columns=train_course_expense_column_mapping, inplace=True)


                df_train_course_teacher_expense = pd.DataFrame(all_teacher_expense_data)
                df_train_course_teacher_expense.rename(columns=train_course_teacher_expense_column_mapping, inplace=True)
                df_train_course_trainee = pd.DataFrame(all_trainee_data)
                df_train_course_trainee.rename(columns=train_course_trainee_column_mapping, inplace=True)

                # 创建并保存Excel文件
                try:
                    # 创建并保存Excel文件
                    with pd.ExcelWriter('train_courses_data.xlsx', engine='openpyxl') as writer:
                        df_train_course.to_excel(writer, sheet_name='Sheet1', index=False)
                        df_train_course_expense.to_excel(writer, sheet_name='Sheet2', index=False)
                        df_train_course_teacher_expense.to_excel(writer, sheet_name='Sheet3', index=False)
                        df_train_course_trainee.to_excel(writer, sheet_name='Sheet4', index=False)

                    return JsonResponse(res201(FILE_DOWNLOAD_SUCCESS))
                except PermissionError:
                    # 处理文件权限问题
                    return JsonResponse(res400(FILE_PERMISSION_DENIED))
            else:
                return JsonResponse(res400(FILE_DOWNLOAD_FAILED))  # 使用正确的状态码/消息

def parse_excel_to_trainees(file_path):
    """
    解析Excel文件内容为Trainee数据列表。

    :param file_path: 字符串，表示Excel文件的本地路径。
    :return: 一个字典列表，每个字典代表一个学员的数据。
    """
    try:
        # 直接加载指定路径的Excel文件
        workbook = openpyxl.load_workbook(filename=file_path, read_only=True, data_only=True)
        sheet = workbook['报名表']

        # 初始化数据列表
        train_course_trainees_datas = []

        # 从第3行开始遍历数据（假设前两行为表头）
        for row_idx, rowValues in enumerate(sheet.iter_rows(min_row=3, values_only=True), start=3):
            # 构建item前先检查是否全为空
            if all(value is None for value in rowValues):  # 确保不处理全为空的行
                continue
            item = {
                'name': rowValues[1] if rowValues[1] else None,  # 假设名字在第二列（根据Python的0-indexing调整）
                'gender': rowValues[2] if rowValues[2] else None,
                'unit_name': rowValues[3] if rowValues[3] else None,
                'branch': rowValues[4] if rowValues[4] else None,
                'post': rowValues[5] if rowValues[5] else None,
                'phone_num': rowValues[6] if rowValues[6] else None,
                'jcc_num': rowValues[7] if rowValues[7] else None,
                'source': rowValues[8] if rowValues[8] else None,
                'remark': rowValues[9] if rowValues[9] else None,
            }
            train_course_trainees_datas.append(item)

        # 检查数据列表是否为空
        if not train_course_trainees_datas:
            raise ValueError("No trainees found in the Excel file.")

        return train_course_trainees_datas

    except (ValueError, FileNotFoundError, openpyxl.utils.exceptions.InvalidFileException) as e:
        print(f"Error parsing Excel file: {str(e)}")
        raise
class ProExamTrainCourseView(viewsets.ModelViewSet):
    serializer_class = TrainCourseSerializer
    authentication_classes = [MyAuthentication, ]
    def pre_exam_train(self, request, *args, **kwargs):
        train_course_expense_data = request.data.copy()  # 如果 request.data 是字典，否则可能需要其他方法
        train_course_id = request.data.get("train_course_id")
        try:
            teacher_expense_total = float(train_course_expense_data.get('teacher_expense_total', 0))
            course_site_rent = float(train_course_expense_data.get('course_site_rent', 0))
            course_meal_fee = float(train_course_expense_data.get('course_meal_fee', 0))
            management_cost = float(train_course_expense_data.get('management_cost', 0))
            courseware_cost = float(train_course_expense_data.get('courseware_cost', 0))
            printing_fee = float(train_course_expense_data.get('printing_fee', 0))
            car_fare = float(train_course_expense_data.get('car_fare', 0))
            quarterage = float(train_course_expense_data.get('quarterage', 0))
            file_bag_fee = float(train_course_expense_data.get('file_bag_fee', 0))
            tea_break_fee = float(train_course_expense_data.get('tea_break_fee', 0))
            other_fee = float(train_course_expense_data.get('other_fee', 0))
            tax = float(train_course_expense_data.get('tax', 0))
            person_count = len(train_course_expense_data.get('train_course_trainees', [])) or 1

        except ValueError as e:
            print(f"Error converting string to float: {e}")
            return JsonResponse(res400(DATA_VALIDATION_FAILED))

        # 计算总成本
        train_course_total_cost = teacher_expense_total + \
                                  course_site_rent + \
                                  course_meal_fee + \
                                  management_cost + \
                                  courseware_cost + \
                                  printing_fee + \
                                  car_fare + \
                                  quarterage + \
                                  file_bag_fee + \
                                  tea_break_fee + \
                                  tax + \
                                  other_fee

        # 计算人均费用
        personal_train_expense = train_course_total_cost / person_count
        personal_train_expense = f"{personal_train_expense:.2f}"

        train_course_expense_data['train_course_total_cost'] = train_course_total_cost  # 添加新字段
        train_course_expense_data['personal_train_expense'] = personal_train_expense  # 添加新字段

        train_course_expense_serializer = TrainCourseExpenseSerializer(data=train_course_expense_data)
        if train_course_expense_serializer.is_valid():
            train_course_expense_serializer.save()
        else:
            TrainCourse.objects.filter(id=train_course_id).delete()
            TrainCourseExpense.objects.filter(train_course_id=train_course_id).delete()
            TrainCourseTeacherExpense.objects.filter(train_course_id=train_course_id).delete()
            return JsonResponse(res400(REQUEST_POST_FAILED))
        train_course_trainee_datas = request.data.get('train_course_trainees', [])
        # 校验 train_course_trainees 列表的长度是否与 person_count 一致
        person_count = TrainCourse.objects.get(id=train_course_id).person_count
        if len(train_course_trainee_datas) != person_count:
            error_message = "提供的学员数据数量与 person_count 不一致。"
            TrainCourse.objects.filter(id=train_course_id).delete()
            TrainCourseExpense.objects.filter(train_course_id=train_course_id).delete()
            TrainCourseTeacherExpense.objects.filter(train_course_id=train_course_id).delete()
            return Response({"msg": error_message}, status=status.HTTP_400_BAD_REQUEST)

        for train_course_trainee_data in train_course_trainee_datas:
            credentials_num = generate_credentials_num(train_course_id)
            train_course_trainee_data['credentials_num'] = credentials_num  # 添加新字段
            train_course_trainee_data['train_course_id'] = train_course_id  # 添加新字段
            train_course_trainee_serializer = TrainCourseTraineeSerializer(
                data=train_course_trainee_data)
            if train_course_trainee_serializer.is_valid():
                new_train_course_trainee = train_course_trainee_serializer.save()
                item = {'name': new_train_course_trainee.name, 'gender': new_train_course_trainee.gender,
                        'unit_name': new_train_course_trainee.unit_name, 'branch': new_train_course_trainee.branch,
                        'source': new_train_course_trainee.source,
                        'post': new_train_course_trainee.post, 'phone_num': new_train_course_trainee.phone_num,
                        'jcc_num': new_train_course_trainee.jcc_num, 'remark': new_train_course_trainee.remark}
                trainee = Trainee.objects.filter(jcc_num=new_train_course_trainee.jcc_num)
                if trainee.count() > 0:
                    # 存在学员更新
                    trainee.update(**item)
                else:
                    # 不存在学员创建
                    Trainee.objects.create(**item)
            else:
                TrainCourse.objects.filter(id=train_course_id).delete()
                TrainCourseExpense.objects.filter(train_course_id=train_course_id).delete()
                TrainCourseTeacherExpense.objects.filter(train_course_id=train_course_id).delete()
                return JsonResponse(res400(REQUEST_POST_FAILED))

        return JsonResponse(res201(REQUEST_POST_SUCCESS))



    def create_pre_exam_train_course(self, request, *args, **kwargs):
        try:
            with ((transaction.atomic())):
                train_course_serializer = TrainCourseSerializer(data=request.data)
                if (train_course_serializer.is_valid()):
                    new_instance = train_course_serializer.save()
                    train_course_id = new_instance.id

                    teacher_expense_total = 0
                    teacher_expense_datas = request.data.get('teacher_expenses', [])
                    for teacher_expense_data in teacher_expense_datas:
                        teacher_expense_data['train_course_id'] = train_course_id  # 添加新字段
                        teacher_expense_total = float(teacher_expense_total) + float(
                            teacher_expense_data['teacher_expense'])

                        train_course_teacher_expense_serializer = TrainCourseTeacherExpenseSerializer(
                            data=teacher_expense_data)
                        if train_course_teacher_expense_serializer.is_valid():
                            train_course_teacher_expense_serializer.save()
                        else:
                            return JsonResponse(res400(REQUEST_POST_FAILED))

                    expenses = TrainCourseTeacherExpense.objects.filter(train_course_id=train_course_id)

                    train_course_contents = [
                        {
                            'id': expense.id,
                            'train_course_content': expense.train_course_content
                        }
                        for expense in expenses
                    ]

                    courses_data = {"train_course_contents": train_course_contents, "teacher_expense_total": teacher_expense_total, "train_course_id": train_course_id}
                    return JsonResponse(res200(REQUEST_POST_SUCCESS, courses_data))
                else:
                    return JsonResponse(res400(REQUEST_POST_FAILED))

        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))

def get_teacher_course_by_id(course_ids):
    try:
        if  ',' in course_ids:
            split_ids = course_ids.split(',')
            course_ids = [id.strip() for id in split_ids if id.strip()]
        else:
            course_ids = [course_ids]

        teacher_expenses = TrainCourseTeacherExpense.objects.filter(id__in=course_ids)
        if teacher_expenses.count() > 0 :
            course_contents = set()
            for expense in teacher_expenses:
                course_contents.add(expense.train_course_content)
                # 将集合转换为列表，并构造所需的字典格式
            return list(course_contents)
        else:
            return None

    except Exception as e:
        return None
def get_teacher_course_by_name(train_course_contents, train_course_id):
    try:

        teacher_expenses = TrainCourseTeacherExpense.objects.filter(train_course_content__in=train_course_contents, train_course_id=train_course_id)

        if teacher_expenses.count() > 0:
            data = [
                    {
                        "id": teacher_expense.id,
                        "train_course_content": teacher_expense.train_course_content,
                    } for teacher_expense in teacher_expenses
                ]
            # 解析出所有id，并用逗号连接成字符串
            train_course_ids = ','.join(str(item['id']) for item in data)
            return train_course_ids
        else:
            return None

    except Exception as e:
        return None
def string_to_boolean(value):
    if value == "是":
        return True
    elif value == "否":
        return False
    else:
        # 处理其他可能的值，或者抛出异常
        return None

def generate_credentials_num(train_course_id):
    """
    生成证书编号
    """
    # 获取课程开始时间的年份
    course_start_time = TrainCourse.objects.get(id=train_course_id).course_start_time
    year = course_start_time.year

    # 计算班次（即本年度内课程的顺序）
    current_year_courses = TrainCourse.objects.filter(course_start_time__year=year)
    class_num = current_year_courses.filter(course_start_time__lte=course_start_time).count()

    # 计算证书顺序号（即本班级内的学员顺序）
    train_course_ids = [train_course.id for train_course in current_year_courses]
    latest_trainee = TrainCourseTrainee.objects.filter(train_course_id__in=train_course_ids).order_by('-id').first()

    # 检查是否找到了记录
    if latest_trainee:
        # 检查credentials_num是否非空
        if latest_trainee.credentials_num:
            # 获取credentials_num的最后五位
            last_five_digits = latest_trainee.credentials_num[-5:]
            # 将字符串转换为整数，进行加1操作，然后再转换回字符串
            cert_seq_num = int(last_five_digits) + 1
        else:
            # 如果credentials_num为空，初始化为默认值
            cert_seq_num = 1
    else:
        cert_seq_num = 1  # 如果没有找到记录，初始化序号为1

    # 格式化证书编号
    credentials_num = f"{year}{str(class_num).zfill(2)}{str(cert_seq_num).zfill(5)}"

    return credentials_num


def set_column_widths(ws, default_width=25):
    # 获取sheet的最大列数
    max_col = ws.max_column
    # 遍历所有列，并设置宽度
    for col_num in range(1, max_col + 1):
        col_char = openpyxl.utils.get_column_letter(col_num)
        ws.column_dimensions[col_char].width = default_width

        # 遍历所有的sheet并设置列宽
class MyCourseView(viewsets.ModelViewSet):
    serializer_class = TrainCourseSerializer
    authentication_classes = [MyAuthentication, ]

    def list(self, request, *args, **kwargs):
        try:
            page, size =  request.query_params.get('page', ''), request.query_params.get('size', '')
            user = request.user
            # 执行查询
            res = TrainCourse.objects.filter(Q(user1_id=user.id) | Q(user2_id=user.id))
            paginator = Paginator(res, int(size))
            try:
                tar_page = paginator.page(page)
            except PageNotAnInteger:
                tar_page = paginator.page(1)
            except EmptyPage:
                tar_page = paginator.page(paginator.num_pages)
            final_data = TrainCourseSerializer(tar_page, many=True)
            return JsonResponse(res200(REQUEST_GET_SUCCESS, final_data.data, res.count()))
        except Exception as e:
            return JsonResponse(res400(str(e)))

class TitleView(viewsets.ModelViewSet):
    serializer_class = TrainCourseSerializer
    authentication_classes = [MyAuthentication, ]

    def list(self, request, *args, **kwargs):
        try:
            # 统计培训班总数
            total_courses = TrainCourse.objects.count()

            # 统计教师总数（注意：这里使用 Python 集合来去重）
            teacher_ids = set(TrainCourseTeacherExpense.objects.values_list('teacher_id', flat=True))
            total_teachers = len(teacher_ids)

            # 统计学员总数
            total_trainees = TrainCourseTrainee.objects.count()

            # 统计费用总数
            total_expense = TrainCourseTrainee.objects.aggregate(Sum('personal_train_expense'))[
                                'personal_train_expense__sum'] or 0

            data = {
                'total_courses': total_courses,
                'total_teachers': total_teachers,
                'total_trainees': total_trainees,
                'total_expense': f"{total_expense:.2f}",
            }
            return JsonResponse(res200('培训班信息统计', data))
        except Exception as e:
            return JsonResponse(res400(str(e)))
def get_trainee_count_for_course_id(train_course_id):
    # 查询特定train_course_id的所有TrainCourseTrainee记录
    averages = {}

    train_course_trainees = TrainCourseTrainee.objects.filter(train_course_id=train_course_id)
     # 初始化一个Counter对象来统计ID出现的次数
    course_id_counter = Counter()
    course_ids = []
    # 遍历所有记录，解析course_ids字符串，并更新计数器
    for trainee in train_course_trainees:
        course_ids_str = trainee.course_ids
        if course_ids_str:  # 确保course_ids_str不是空字符串或None
            course_ids = [int(id.strip()) for id in course_ids_str.strip('[]').split(',')]
            course_id_counter.update(course_ids)
        teacher_expenses = TrainCourseTeacherExpense.objects.filter(id__in=course_ids).values_list('id', 'teacher_expense')
        for id, total_expense in teacher_expenses:
           total_expense = float(total_expense)  # 将费用字符串转换为浮点数
           student_count = course_id_counter.get(id, 0)  # 获取选课学员数量，默认为0
           if student_count > 0:
               average_expense_per_student = total_expense / student_count
               averages[id] = f"{average_expense_per_student:.2f}"
           else:
               averages[id] = 0  # 如果没有选课学员，则平均费用为0
    return averages
